package com.proactive.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.mysql.jdbc.Statement;
import com.proactive.webapp.dao.DesignDAO;
import com.proactive.webapp.entity.Course;
import com.proactive.webapp.entity.Design;
import com.proactive.webapp.entity.Member;
import com.proactive.webapp.util.DateConversion;
import com.proactive.webapp.util.EncodeHtml;

public class DesignDAOImpl implements DesignDAO{
	DateConversion dateConversion = new DateConversion();
	EncodeHtml encodeHtml = new EncodeHtml();
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public int insert(Design design) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO design(designCategoryID, designTitle, designImage, "
				+ "designContent, designDemo, designFile, designFileUrl, isFile, designWriter, designWriterUrl, "
				+ "isWriter, memberAccount, designStatus, designFileName, createDate, updateDate) VALUES"
				+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())";
		int designID = 0; 
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			smt.setInt(1, design.getCategoryID());
			smt.setString(2, design.getTitle());
			smt.setString(3, design.getImagePath());
			smt.setString(4, design.getContent());
			smt.setString(5, design.getDemo());
			smt.setString(6, design.getFilePath());
			smt.setString(7, design.getFileUrl());
			smt.setInt(8, design.getIsFile());
			smt.setString(9, design.getWriter());
			smt.setString(10, design.getWriterUrl());
			smt.setInt(11, design.getIsWriter());
			smt.setString(12, design.getAccount());
			smt.setInt(13, design.getStatus());
			smt.setString(14, design.getFileName());
			smt.executeUpdate();
			rs = smt.getGeneratedKeys();
			if(rs.next()){
				designID = rs.getInt(1);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return designID;
	}
	
	public int insertPractice(Design design) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO design(designCategoryID, designTitle, designImage, "
				+ "designContent, designDemo, designFile, designFileUrl, isFile, isPractice, writerMemberAccount, "
				+ "courseID, isWriter, memberAccount, designStatus, designFileName, createDate, updateDate) VALUES"
				+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())";
		int designID = 0; 
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			smt.setInt(1, design.getCategoryID());
			smt.setString(2, design.getTitle());
			smt.setString(3, design.getImagePath());
			smt.setString(4, design.getContent());
			smt.setString(5, design.getDemo());
			smt.setString(6, design.getFilePath());
			smt.setString(7, design.getFileUrl());
			smt.setInt(8, design.getIsFile());
			smt.setInt(9, design.getIsPractice());
			smt.setString(10, design.getWriterAccount());
			smt.setInt(11, design.getCid());
			smt.setInt(12, design.getIsWriter());
			smt.setString(13, design.getAccount());
			smt.setInt(14, design.getStatus());
			smt.setString(15, design.getFileName());
			smt.executeUpdate();
			rs = smt.getGeneratedKeys();
			if(rs.next()){
				designID = rs.getInt(1);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return designID;
	}

	public void update(Design design) {
		// TODO Auto-generated method stub
		String sql = "UPDATE design SET designCategoryID=?, designTitle=?, designImage=?, "
				+ "designContent=?, designDemo=?, designFileName=?, designFile=?, "
				+ "designFileUrl=?, isFile=?, designWriter=?, designWriterUrl=?, isWriter=?, "
				+ "updateDate=NOW() WHERE designID=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, design.getCategoryID());
			smt.setString(2, design.getTitle());
			smt.setString(3, design.getImagePath());
			smt.setString(4, design.getContent());
			smt.setString(5, design.getDemo());
			smt.setString(6, design.getFileName());
			smt.setString(7, design.getFilePath());
			smt.setString(8, design.getFileUrl());
			smt.setInt(9, design.getIsFile());
			smt.setString(10, design.getWriter());
			smt.setString(11, design.getWriterUrl());
			smt.setInt(12, design.getIsWriter());
			smt.setInt(13, design.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void updateStatus(Design design) {
		// TODO Auto-generated method stub
		String sql = "UPDATE design SET designStatus = ? WHERE designID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, design.getStatus());
			smt.setInt(2, design.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void updateViewCount(Design design) {
		// TODO Auto-generated method stub
		String sql = "UPDATE design SET designViewCount = ? WHERE designID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, design.getViewCount());
			smt.setInt(2, design.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public Design get(Design design) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
				+ "AS reportCount FROM design a JOIN design_category e ON a.designCategoryID = e.designCategoryID "
				+ "JOIN member f ON a.memberAccount = f.memberAccount "
				+ "LEFT JOIN member writer ON a.writerMemberAccount = writer.memberAccount "
				+ "LEFT JOIN course h ON a.courseID = h.courseID "
				+ "WHERE designID = ? HAVING reportCount < 10";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, design.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("f.memberAccount"));
				member.setName(rs.getString("f.memberName"));
				member.setJob(rs.getString("f.memberJob"));
				member.setMemberNO(rs.getString("f.memberNO"));
				Member writerMember = new Member();
				writerMember.setAccount(rs.getString("writer.memberAccount"));
				writerMember.setName(rs.getString("writer.memberName"));
				writerMember.setJob(rs.getString("writer.memberJob"));
				writerMember.setMemberNO(rs.getString("writer.memberNO"));
				Course course = new Course();
				course.setTitle(encodeHtml.getGist(10, rs.getString("courseTitle")));
				design.setMember(member);
				design.setWriterMember(writerMember);
				design.setCourse(course);
				design.setCategoryName(rs.getString("designCategoryName"));
				design.setCategoryID(rs.getInt("designCategoryID"));
				design.setTitle(rs.getString("designTitle"));
				design.setContent(rs.getString("designContent"));
				design.setImagePath(rs.getString("designImage"));
				design.setDemo(rs.getString("designDemo"));				
				design.setIsFile(rs.getInt("isFile"));
				design.setFileName(rs.getString("designFileName"));
				design.setFilePath(rs.getString("designFile"));
				design.setDownloadUrl(design.getFilePath().equals("")?"":("download?t=2&id="+design.getId()));
				design.setFileUrl(rs.getString("designFileUrl"));
				design.setIsWriter(rs.getInt("isWriter"));
				design.setIsPractice(rs.getInt("isPractice"));
				design.setWriter(design.getIsPractice()==0?rs.getString("designWriter"):writerMember.getName());
				design.setWriterUrl(design.getIsPractice()==0?rs.getString("designWriterUrl"):"aboutme?id="+writerMember.getMemberNO());
				design.setIsPractice(rs.getInt("isPractice"));
				design.setCid(rs.getInt("courseID"));
				design.setWriterAccount(rs.getString("writerMemberAccount"));
				design.setViewCount(rs.getInt("designViewCount"));
				design.setFavoriteCount(rs.getInt("favoriteCount"));
				design.setCommentCount(rs.getInt("commentCount"));
				design.setAccount(rs.getString("memberAccount"));
				design.setStatus(rs.getInt("designStatus"));
				design.setCreateDate(rs.getDate("createDate"));
				design.setCreateDateString(dateConversion.ConversionYDM(rs.getDate("createDate")));
				design.setCategoryUrl("design?c1="+design.getCategoryID());
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return design;
	}
	
	public Design getByWriter(Design design) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
				+ "AS reportCount FROM design a JOIN design_category e ON a.designCategoryID = e.designCategoryID "
				+ "JOIN member f ON a.memberAccount = f.memberAccount "
				+ "LEFT JOIN course g ON a.courseID = g.courseID "
				+ "LEFT JOIN member writer ON a.writerMemberAccount = writer.memberAccount "
				+ "WHERE designID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, design.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setMemberNO(rs.getString("memberNO"));
				Member writerMember = new Member();
				writerMember.setAccount(rs.getString("writer.memberAccount"));
				writerMember.setName(rs.getString("writer.memberName"));
				writerMember.setJob(rs.getString("writer.memberJob"));
				writerMember.setMemberNO(rs.getString("writer.memberNO"));
				Course course = new Course();
				course.setTitle(encodeHtml.getGist(10, rs.getString("courseTitle")));
				design.setMember(member);
				design.setWriterMember(writerMember);
				design.setCourse(course);
				design.setCategoryName(rs.getString("designCategoryName"));
				design.setCategoryID(rs.getInt("designCategoryID"));
				design.setTitle(rs.getString("designTitle"));
				design.setContent(rs.getString("designContent"));
				design.setImagePath(rs.getString("designImage"));
				design.setDemo(rs.getString("designDemo"));		
				design.setIsFile(rs.getInt("isFile"));
				design.setFileName(rs.getString("designFileName"));
				design.setFilePath(rs.getString("designFile"));
				design.setDownloadUrl(design.getFilePath().equals("")?"":("download?t=2&id="+design.getId()));
				design.setFileUrl(rs.getString("designFileUrl"));
				design.setIsWriter(rs.getInt("isWriter"));
				design.setWriter(rs.getString("designWriter"));
				design.setWriterUrl(rs.getString("designWriterUrl"));
				design.setIsPractice(rs.getInt("isPractice"));
				design.setCid(rs.getInt("courseID"));
				design.setViewCount(rs.getInt("designViewCount"));
				design.setFavoriteCount(rs.getInt("favoriteCount"));
				design.setCommentCount(rs.getInt("commentCount"));
				design.setAccount(rs.getString("memberAccount"));
				design.setStatus(rs.getInt("designStatus"));
				design.setCreateDate(rs.getDate("createDate"));
				design.setCreateDateString(dateConversion.ConversionYDM(rs.getDate("createDate")));
				design.setCategoryUrl("design?c1="+design.getCategoryID());
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return design;
	}
	
	@Override
	public Design getByLogin(Design design, String loginAccount) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
				+ "AS reportCount,(SELECT COUNT(*) FROM design_favorite e WHERE a.designID = e.designID AND e.memberAccount=?) "
				+ "AS favorite,(SELECT COUNT(*) FROM design_collect f WHERE a.designID = f.designID AND f.memberAccount=?) "
				+ "AS collect,(SELECT COUNT(*) FROM design_report g WHERE a.designID = g.designID AND g.memberAccount=?) "
				+ "AS report FROM design a JOIN design_category h ON a.designCategoryID = h.designCategoryID "
				+ "JOIN member i ON a.memberAccount = i.memberAccount "
				+ "LEFT JOIN member writer ON a.writerMemberAccount = writer.memberAccount "
				+ "LEFT JOIN course j ON a.courseID = j.courseID "
				+ "WHERE designID = ? HAVING reportCOunt < 10";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, loginAccount);
			smt.setString(3, loginAccount);
			smt.setInt(4, design.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("i.memberAccount"));
				member.setName(rs.getString("i.memberName"));
				member.setJob(rs.getString("i.memberJob"));
				member.setMemberNO(rs.getString("i.memberNO"));
				Member writerMember = new Member();
				writerMember.setAccount(rs.getString("writer.memberAccount"));
				writerMember.setName(rs.getString("writer.memberName"));
				writerMember.setJob(rs.getString("writer.memberJob"));
				writerMember.setMemberNO(rs.getString("writer.memberNO"));
				Course course = new Course();
				course.setTitle(encodeHtml.getGist(10, rs.getString("courseTitle")));
				design.setMember(member);
				design.setWriterMember(writerMember);
				design.setCourse(course);
				design.setCategoryName(rs.getString("designCategoryName"));
				design.setCategoryID(rs.getInt("designCategoryID"));
				design.setTitle(rs.getString("designTitle"));
				design.setContent(rs.getString("designContent"));
				design.setImagePath(rs.getString("designImage"));
				design.setDemo(rs.getString("designDemo"));		
				design.setIsFile(rs.getInt("isFile"));
				design.setFileName(rs.getString("designFileName"));
				design.setFilePath(rs.getString("designFile"));
				design.setDownloadUrl(design.getFilePath().equals("")?"":("download?t=2&id="+design.getId()));
				design.setFileUrl(rs.getString("designFileUrl"));
				design.setIsWriter(rs.getInt("isWriter"));
				design.setIsPractice(rs.getInt("isPractice"));
				design.setWriter(design.getIsPractice()==0?rs.getString("designWriter"):writerMember.getName());
				design.setWriterUrl(design.getIsPractice()==0?rs.getString("designWriterUrl"):"aboutme?id="+writerMember.getMemberNO());
				design.setIsPractice(rs.getInt("isPractice"));
				design.setCid(rs.getInt("courseID"));
				design.setWriterAccount(rs.getString("writerMemberAccount"));
				design.setViewCount(rs.getInt("designViewCount"));
				design.setFavoriteCount(rs.getInt("favoriteCount"));
				design.setCommentCount(rs.getInt("commentCount"));
				design.setAccount(rs.getString("memberAccount"));
				design.setStatus(rs.getInt("designStatus"));
				design.setCreateDate(rs.getDate("createDate"));
				design.setCreateDateString(dateConversion.ConversionYDM(rs.getDate("createDate")));
				design.setCategoryUrl("design?c1="+design.getCategoryID());
				design.setCollectFlag(rs.getInt("collect")==0?false:true);
				design.setFavoriteFlag(rs.getInt("favorite")==0?false:true);
				design.setReportFlag(rs.getInt("report")==0?false:true);
				design.setWriterFlag(member.getAccount().equals(loginAccount)?true:false);
				design.setFavoriteString1(design.isFavoriteFlag()?"none":"inline");
				design.setFavoriteString2(design.isFavoriteFlag()?"inline":"none");
				design.setFavoriteClass(design.isFavoriteFlag()?"topfocusLove":"");
				design.setCollectString(design.isCollectFlag()?"已收藏":"收藏");
				design.setCollectString1(design.isCollectFlag()?"none":"inline");
				design.setCollectString2(design.isCollectFlag()?"inline":"none");
				design.setCollectClass(design.isCollectFlag()?"topfocusCollect":"");
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return design;
	}

	public List<Design> getListByType(String type, int start, int size) {
		// TODO Auto-generated method stub
		List<Design> designList = new ArrayList<Design>();
		String sql = "";
		switch(type){
			case "new":
				sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
						+ "AS reportCount FROM design a WHERE designStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
			case "hot":
				sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
						+ "AS reportCount FROM design a WHERE designStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY designViewCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "com":
				sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
						+ "AS reportCount FROM design a WHERE designStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY commentCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "fav":
				sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
						+ "AS reportCount FROM design a WHERE designStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY favoriteCount DESC, createDate DESC LIMIT ?,?";
				break;
			default:
				sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
						+ "AS reportCount FROM design a WHERE designStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
		}
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, start);
			smt.setInt(2, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Design design = new Design();
				design.setId(rs.getInt("designID"));
				design.setTitle(rs.getString("designTitle"));
				design.setImagePath(rs.getString("designImage"));
				design.setIsFile(rs.getInt("isFile"));
				design.setFileName(rs.getString("designFileName"));
				design.setFilePath(rs.getString("designFile"));
				design.setDownloadUrl(design.getFilePath().equals("")?"":("download?t=2&id="+design.getId()));
				design.setFileUrl(rs.getString("designFileUrl"));
				design.setViewCount(rs.getInt("designViewCount"));
				design.setFavoriteCount(rs.getInt("favoriteCount"));
				design.setCommentCount(rs.getInt("commentCount"));
				design.setIsPractice(rs.getInt("isPractice"));
				design.setPracticeClass(design.getIsPractice()==1?"practice":"");
				designList.add(design);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return designList;
	}

	public List<Design> getListByCategory(String type, int designCategoryID, int start, int size) {
		// TODO Auto-generated method stub
		List<Design> designList = new ArrayList<Design>();
		String sql = "";
		switch(type){
			case "new":
				sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
						+ "AS reportCount FROM design a WHERE designCategoryID = ? AND designStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
			case "hot":
				sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
						+ "AS reportCount FROM design a WHERE designCategoryID = ? AND designStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY designViewCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "com":
				sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
						+ "AS reportCount FROM design a WHERE designCategoryID = ? AND designStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY commentCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "fav":
				sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
						+ "AS reportCount FROM design a WHERE designCategoryID = ? AND designStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY favoriteCount DESC, createDate DESC LIMIT ?,?";
				break;
			default:
				sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
						+ "AS reportCount FROM design a WHERE designCategoryID = ? AND designStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
		}
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, designCategoryID);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Design design = new Design();
				design.setId(rs.getInt("designID"));
				design.setTitle(rs.getString("designTitle"));
				design.setImagePath(rs.getString("designImage"));
				design.setIsFile(rs.getInt("isFile"));
				design.setFileName(rs.getString("designFileName"));
				design.setFilePath(rs.getString("designFile"));
				design.setDownloadUrl(design.getFilePath().equals("")?"":("download?t=2&id="+design.getId()));
				design.setFileUrl(rs.getString("designFileUrl"));
				design.setViewCount(rs.getInt("designViewCount"));
				design.setFavoriteCount(rs.getInt("favoriteCount"));
				design.setCommentCount(rs.getInt("commentCount"));
				design.setIsPractice(rs.getInt("isPractice"));
				design.setPracticeClass(design.getIsPractice()==1?"practice":"");
				designList.add(design);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return designList;
	}

	
	@Override
	public int countTotal(int designCategoryID) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			if(designCategoryID != 0){
				String sql = "SELECT *,(SELECT COUNT(*) FROM design_report b WHERE a.designID = b.designID HAVING COUNT(*)<10) "
						+ "AS reportCount FROM design a WHERE designCategoryID = ? AND designStatus = 1 "
						+ "HAVING reportCount IS NOT NULL";	
				smt = conn.prepareStatement(sql);
				smt.setInt(1, designCategoryID);
			}
			else{
				String sql = "SELECT *,(SELECT COUNT(*) FROM design_report b WHERE a.designID = b.designID HAVING COUNT(*)<10) "
						+ "AS reportCount FROM design a WHERE designStatus = 1 HAVING reportCount IS NOT NULL";	
				smt = conn.prepareStatement(sql);
			}
			
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count = rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	

	@Override
	public List<Design> getListByCategoryTop3(int categoryID, int currentDesignID) {
		// TODO Auto-generated method stub
		List<Design> designList = new ArrayList<Design>();		
		String sql = "SELECT *,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
				+ "AS reportCount FROM design a JOIN design_category c ON a.designCategoryID = c.designCategoryID "
				+ "WHERE a.designCategoryID = ? AND designID != ? AND designStatus = 1 HAVING reportCount < 10 "
				+ "ORDER BY createDate DESC LIMIT 3";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, categoryID);
			smt.setInt(2, currentDesignID);
			rs = smt.executeQuery();
			while(rs.next()){
				Design design = new Design();
				design.setId(rs.getInt("designID"));
				design.setTitle(rs.getString("designTitle"));
				design.setImagePath(rs.getString("designImage"));
				design.setIsFile(rs.getInt("isFile"));
				design.setFileName(rs.getString("designFileName"));
				design.setFilePath(rs.getString("designFile"));
				design.setDownloadUrl(design.getFilePath().equals("")?"":("download?t=2&id="+design.getId()));
				design.setFileUrl(rs.getString("designFileUrl"));
				designList.add(design);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return designList;
	}

	@Override
	public List<Design> getPracticeListByTop(int courseID, int top) {
		// TODO Auto-generated method stub
		List<Design> designList = new ArrayList<Design>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM design_report b WHERE a.designID = b.designID) "
				+ "AS reportCount FROM design a WHERE courseID = ? AND designStatus = 1 "
				+ "HAVING reportCount < 10 ORDER BY createDate DESC LIMIT ?";		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, courseID);
			smt.setInt(2, top);
			rs = smt.executeQuery();
			while(rs.next()){
				Design design = new Design();
				design.setId(rs.getInt("designID"));
				design.setCid(rs.getInt("courseID"));
				design.setTitle(rs.getString("designTitle"));
				design.setImagePath(rs.getString("designImage"));
				design.setFileUrl(rs.getString("designFileUrl"));
				designList.add(design);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return designList;
	}

	@Override
	public List<Design> getPracticeList(int courseID, int start, int size) {
		// TODO Auto-generated method stub
		List<Design> designList = new ArrayList<Design>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
				+ "AS reportCount FROM design a WHERE courseID = ? AND designStatus = 1 HAVING reportCount < 10 "
				+ "ORDER BY createDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, courseID);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Design design = new Design();
				design.setId(rs.getInt("designID"));
				design.setTitle(rs.getString("designTitle"));
				design.setImagePath(rs.getString("designImage"));
				design.setIsFile(rs.getInt("isFile"));
				design.setFileName(rs.getString("designFileName"));
				design.setFilePath(rs.getString("designFile"));
				design.setDownloadUrl(design.getFilePath().equals("")?"":("download?t=2&id="+design.getId()));
				design.setFileUrl(rs.getString("designFileUrl"));
				design.setViewCount(rs.getInt("designViewCount"));
				design.setFavoriteCount(rs.getInt("favoriteCount"));
				design.setCommentCount(rs.getInt("commentCount"));
				design.setIsPractice(rs.getInt("isPractice"));
				design.setPracticeClass(design.getIsPractice()==1?"practice":"");
				designList.add(design);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return designList;
	}

	@Override
	public int countPracticeTotal(int courseID) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			String sql = "SELECT *,(SELECT COUNT(*) FROM design_report b WHERE a.designID = b.designID HAVING COUNT(*)<10) "
					+ "AS reportCount FROM design a WHERE courseID = ? AND designStatus = 1 "
					+ "HAVING reportCount IS NOT NULL";	
			smt = conn.prepareStatement(sql);
			smt.setInt(1, courseID);				
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count = rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public List<Design> getListByMember(String account, int start, int size) {
		// TODO Auto-generated method stub
		List<Design> designList = new ArrayList<Design>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
				+ "AS reportCount FROM design a WHERE memberAccount = ? AND designStatus = 1 HAVING reportCount < 10 "
				+ "ORDER BY createDate DESC LIMIT ?,?";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Design design = new Design();
				design.setId(rs.getInt("designID"));
				design.setTitle(rs.getString("designTitle"));
				design.setImagePath(rs.getString("designImage"));
				design.setIsFile(rs.getInt("isFile"));
				design.setFileName(rs.getString("designFileName"));
				design.setFilePath(rs.getString("designFile"));
				design.setDownloadUrl(design.getFilePath().equals("")?"":("download?t=2&id="+design.getId()));
				design.setFileUrl(rs.getString("designFileUrl"));
				design.setStatus(rs.getInt("designStatus"));
				design.setIsPractice(rs.getInt("isPractice"));
				design.setPracticeClass(design.getIsPractice()==1?"practice":"");
				design.setCid(rs.getInt("courseID"));
				design.setViewCount(rs.getInt("designViewCount"));
				design.setFavoriteCount(rs.getInt("favoriteCount"));
				design.setCommentCount(rs.getInt("commentCount"));
				designList.add(design);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return designList;
	}

	@Override
	public List<Design> getListByWriter(String account, int start, int size) {
		// TODO Auto-generated method stub
		List<Design> designList = new ArrayList<Design>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM design_comment b WHERE topCommentID IS NULL AND a.designID = b.designID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM design_favorite c WHERE a.designID = c.designID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM design_report d WHERE a.designID = d.designID) "
				+ "AS reportCount FROM design a WHERE memberAccount = ? AND designStatus != 3 HAVING reportCount < 10 "
				+ "ORDER BY createDate DESC LIMIT ?,?";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Design design = new Design();
				design.setId(rs.getInt("designID"));
				design.setTitle(rs.getString("designTitle"));
				design.setImagePath(rs.getString("designImage"));
				design.setIsFile(rs.getInt("isFile"));
				design.setFileName(rs.getString("designFileName"));
				design.setFilePath(rs.getString("designFile"));
				design.setDownloadUrl(design.getFilePath().equals("")?"":("download?t=2&id="+design.getId()));
				design.setFileUrl(rs.getString("designFileUrl"));
				design.setStatus(rs.getInt("designStatus"));
				design.setIsPractice(rs.getInt("isPractice"));
				design.setPracticeClass(design.getIsPractice()==1?"practice":"");
				design.setCid(rs.getInt("courseID"));
				design.setActString(design.getStatus()==2?"顯示":"隱藏");
				design.setActClass(design.getStatus()==2?"show":"hide");
				design.setHideClass(design.getStatus()==2?"edithide":"");
				design.setViewCount(rs.getInt("designViewCount"));
				design.setFavoriteCount(rs.getInt("favoriteCount"));
				design.setCommentCount(rs.getInt("commentCount"));
				designList.add(design);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return designList;
	}

	@Override
	public int countTotalByMember(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			String sql = "SELECT *,(SELECT COUNT(*) FROM design_report b WHERE a.designID = b.designID HAVING COUNT(*)<10) "
					+ "AS reportCount FROM design a WHERE memberAccount = ? AND designStatus = 1 "
					+ "HAVING reportCount IS NOT NULL";	
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count =rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public int countTotalByWriter(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			String sql = "SELECT *,(SELECT COUNT(*) FROM design_report b WHERE a.designID = b.designID HAVING COUNT(*)<10) "
					+ "AS reportCount FROM design a WHERE memberAccount = ? AND designStatus != 3 HAVING reportCount IS NOT NULL";	
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count =rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

}
